import pymysql
from selenium import webdriver
import time
from selenium.webdriver import ActionChains


driver = webdriver.Chrome("C:\Program Files (x86)\Google\Chrome\Application\chromedriver.exe")
driver.get('https://mail.qq.com/')
driver.switch_to.frame("login_frame")

try:
    driver.find_element_by_xpath('//*[@id="switcher_plogin"]').click()
except:
    pass

driver.find_element_by_xpath('//*[@id="u"]').send_keys("QQ账号")
driver.find_element_by_xpath('//*[@id="p"]').send_keys("QQ密码")
driver.find_element_by_xpath('//*[@id="login_button"]').click()

try:
    driver.switch_to.parent_frame()
    driver.switch_to.default_content()
    time.sleep(3)
    driver.find_element_by_xpath('//*[@id="folder_1"]').click()
    driver.switch_to.frame('mainFrame')
except:
    time.sleep(3)
    driver.find_element_by_xpath('//*[@id="folder_1"]').click()
    driver.switch_to.frame('mainFrame')

titleList=[]
userList=[]
timeList=[]
contentList=[]

html=driver.current_window_handle
emails=driver.find_elements_by_css_selector('.toarea .F, .toarea .M')
for i in range(len(emails)):
    email=driver.find_elements_by_css_selector('.toarea .F, .toarea .M')[i]
    ActionChains(driver).context_click(email).perform()
    driver.find_elements_by_css_selector('.menu_item')[1].click()
    email_detail_handle = driver.window_handles[1]
    driver.switch_to.window(email_detail_handle)
    main_frame=driver.find_element_by_id('mainFrame')
    driver.switch_to.frame(main_frame)
    try:
        send_title=driver.find_elements_by_css_selector('.sub_title')[0].text
        titleList.append(send_title)
        send_author=driver.find_elements_by_css_selector('.grn')[1].text
        userList.append(send_author)
        send_time=driver.find_elements_by_css_selector('#local-time-caption')[0].text
        timeList.append(send_time)
        content=driver.find_elements_by_css_selector('#mailContentContainer')[0].text
        contentList.append(content)
        print([send_title, send_author, send_time, content])
    except IndexError:
        print('element error！')
        pass
    driver.close()
    driver.switch_to.window(html)
    main_frame=driver.find_element_by_id('mainFrame')
    driver.switch_to.frame(main_frame)
driver.quit()

def create_table():
    conn=pymysql.connect(host="localhost",user="root",password="root",db="mysql")
    sql_create = """ 
    create table `email` (
    ``user` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
    `data` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
    `title` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
    `content` longtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
    """
    cursor=conn.cursor()
    cursor.execute("drop table if exists email;")
    cursor.execute(sql_create)
    conn.commit()
    conn.close()


def tosql():
    my_db=pymysql.connect(host="localhost", user="root", password="root", db="mysql")
    cursor=my_db.cursor()
    for i in range(len(titleList)):
        titles="".join(titleList[i])
        users="".join(userList[i])
        times="".join(timeList[i])
        contents="".join(contentList[i])
        sql="INSERT into `email` (title,name,time,content) values (%s,%s,%s,%s)"
        txt_values=[(titles, users,times,contents)]
        cursor.executemany(sql, txt_values)

    my_db.commit()
    my_db.close()

#print(len(titleList))
#print(len(timeList))
#print(len(authorList))
#print(len(contentList))

if __name__ == "__main__":
    create_table()
    tosql()

